package com.affablebean.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.affablebean.model.entity.Customer;

public class CustomerDAL extends AbstractDAL<Customer>{

	public CustomerDAL() {
		super("CUSTOMERS");
	}

	@Override
	public boolean insertEntity(Customer entity) throws SQLException {
		boolean result=false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "INSERT INTO customers VALUES (customer_seq.NEXTVAL, ?,?,?,?,?,?,?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd, new String[] { "customer_id" })) {
				statement.setString(1,entity.getFirstName());
				statement.setString(2,entity.getLastName());
				statement.setString(3,entity.getEmail());
				statement.setString(4,entity.getPhone());
				statement.setString(5,entity.getAdress());
				statement.setString(6,entity.getCityRegion());
				statement.setString(7,entity.getCcNumber());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
						if (generatedKeys.next()) {
							entity.setId(generatedKeys.getInt(1));
						}
					}
				}
			}
		}
		
		return result;
	}

	@Override
	public boolean updateEntity(Customer entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "UPDATE customers SET first_name = ?, last_name = ?, email = ?, phone_number = ?"
					+ "address = ?, city = ?, cc_number = ? where customer_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setString(1,entity.getFirstName());
				statement.setString(2,entity.getLastName());
				statement.setString(3,entity.getEmail());
				statement.setString(4,entity.getPhone());
				statement.setString(5,entity.getAdress());
				statement.setString(6,entity.getCityRegion());
				statement.setString(7,entity.getCcNumber());
				statement.setInt(8,entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean deleteEntity(Customer entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "DELETE FROM customers WHERE customer_id = ?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public List<Customer> getAll() throws SQLException {
		List<Customer> customers = new ArrayList<Customer>();
		try (Connection connection = createNewConnection()) {
			try (Statement statement = connection.createStatement()) {
				final String sqlCmd = "SELECT * FROM customers";
				try (ResultSet resultSet = statement.executeQuery(sqlCmd)) {
					while (resultSet.next()) {
						customers.add(createEntity(resultSet));
					}
				}
			}
		}
		return customers;
	}

	@Override
	public Customer findById(int id) throws SQLException {
		Customer customer = null;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "SELECT * FROM customers WHERE customer_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, id);
				try (ResultSet resultSet = statement.executeQuery()) {
					if (resultSet.next()) customer = createEntity(resultSet);
				}
			}

		}
		return customer;
	}

	@Override
	Customer createEntity(ResultSet resultSet) throws SQLException {
		Customer customer = new Customer();
		customer.setId(resultSet.getInt("customer_id"));
		customer.setFirstName(resultSet.getString("first_name"));
		customer.setLastName(resultSet.getString("last_name"));
		customer.setEmail(resultSet.getString("email"));
		customer.setPhone(resultSet.getString("phone_number"));
		customer.setAdress(resultSet.getString("address"));
		customer.setCityRegion(resultSet.getString("city"));
		customer.setCcNumber(resultSet.getString("cc_number"));
		return customer;
	}

}
